package cn.com.jsepc.rep.report;

import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import cn.com.jsepc.rep.comm.ConstUtil;
import cn.com.jsepc.rep.comm.NumberFormatUtils;
import cn.com.jsepc.rep.demension.CheckLeaf;
import cn.com.jsepc.rep.demension.DementionDAO;
import cn.com.jsepc.rep.vo.*;

import com.jarp.db.DAO;

import javax.servlet.ServletException;

public class QueryData {

     KeyCalc cal=new KeyCalc();

    //加载列
    public String getFillGridCol(String repid,String isedit,String corpid,String stemp) {
        StringBuffer rest = new StringBuffer();
        StringBuffer strBuffer = new StringBuffer();    //jReader
        StringBuffer cmBuffer = new StringBuffer();     //ColumnModel
        try {
        	RepDataVO repVo = getRepDemension(repid);
        	ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
        	if (repVo==null || repDemLst==null || repDemLst.size()==0){
        		return "";
        	}
                strBuffer.append("\n var jReader1 = new Ext.data.JsonReader({ ");
                strBuffer.append("\n     root: 'data',");
                strBuffer.append("\n     totalProperty: 'totalCount',");
                strBuffer.append("\n     fields : [");
                cmBuffer.append("\n var cm = new Ext.grid.ColumnModel( [  ");
                cmBuffer.append("\n	 new Ext.grid.RowNumberer()");
                //先添加维度
                for (int i = 0; i < repDemLst.size(); i++) {
                	RepDemensionVO repDemVO = repDemLst.get(i);
//                    String demidcol = repDemVO.getIdcol();
                    String demfaccol = repDemVO.getFactdimcol();
                    String demavalisname = repDemVO.getAvailsname();
//                    String demname = repDemVO.getDemname();
                    String demnamecol = repDemVO.getNamecol();
                    if (demfaccol != null && !demfaccol.equals("")) {
                        strBuffer.append("\n  {name : '" + demfaccol + "'}");
                        strBuffer.append(" ,");
                        strBuffer.append("\n  {name : '" + demfaccol+demnamecol + "'}");
                        strBuffer.append(" ,");
                        cmBuffer.append("\n	,{");
                        cmBuffer.append("\n	    id : '" + demfaccol + "',");
                        cmBuffer.append("\n	    name :'" + demfaccol + "',");
                        cmBuffer.append("\n	    header : '" + demavalisname + "ID', ");
                        cmBuffer.append("\n	    sortable : false, ");
                        cmBuffer.append("\n	    hidden : true, ");
                        cmBuffer.append("\n	    dataIndex: '" + demfaccol + "'");
                        cmBuffer.append("\n	}");
                        cmBuffer.append("\n	,{");
                        cmBuffer.append("\n	    id : '" + demfaccol+demnamecol + "',");
                        cmBuffer.append("\n	    name :'" + demfaccol+demnamecol + "',");
                        cmBuffer.append("\n	    header : '" + demavalisname + "', ");
                        cmBuffer.append("\n	    sortable : true, ");
                        cmBuffer.append("\n	    dataIndex: '" + demfaccol+demnamecol + "'");
                        cmBuffer.append("\n	}");
                    }
                }
                String repname = repVo.getRepname();
                String factcol = repVo.getRepvaluecol();
                String factsumcol = "SUMVAL";
                String dataprec = repVo.getDataprec();
                String repunit = repVo.getDataunit();
                if (factcol != null && !factcol.equals("")) {
                    strBuffer.append("\n  {name : '" + factcol + "'}");
                    strBuffer.append("\n  ,{name : '" + factsumcol + "'}");
                    strBuffer.append("\n  ,{name : 'YOYRATION'}");
                    strBuffer.append("\n  ,{name : 'MONRATION'}");
                    strBuffer.append("\n  ,{name : 'TYOYRATION'}");
                    strBuffer.append("\n  ,{name : 'TMONRATION'}");
                    strBuffer.append("\n  ,{name : 'PREVAL'}");
                    strBuffer.append("\n  ,{name : 'TPREVAL'}");
                    strBuffer.append("\n  ,{name : 'TREPVAL'}");
                    strBuffer.append("\n  ,{name : 'TSUMVAL'}");
                    cmBuffer.append("\n	,{");
                    cmBuffer.append("\n	     name :'" + factcol + "',");
                    cmBuffer.append("\n	     header : '当期值', ");
                    cmBuffer.append("\n	     dataIndex: '" + factcol + "',");
                    if (isedit!=null && isedit.equals("1")){
	                    cmBuffer.append("\n	     editor: new Ext.form.Field({");
	                    cmBuffer.append("\n	         allowBlank: true,");
	                    cmBuffer.append("\n	         decimalPrecision : " + dataprec + ",");
	                    cmBuffer.append("\n	         allowNegative: false");
	                    cmBuffer.append("\n	     }),");
                    }
                    cmBuffer.append("\n	     sortable : false ");
                    cmBuffer.append("\n	  }");
                    
                    cmBuffer.append("\n	,{");
                    cmBuffer.append("\n	     name :'" + factsumcol + "',");
                    cmBuffer.append("\n	     header : '累计值', ");
                    cmBuffer.append("\n	     dataIndex: '" + factsumcol + "',");
                    if (isedit!=null && isedit.equals("1")){
	                    cmBuffer.append("\n	     editor: new Ext.form.Field({");
	                    cmBuffer.append("\n	         allowBlank: true,");
	                    cmBuffer.append("\n	         decimalPrecision : " + dataprec + ",");
	                    cmBuffer.append("\n	         allowNegative: false");
	                    cmBuffer.append("\n	     }),");
                    }
                    cmBuffer.append("\n	     sortable : false ");
                    cmBuffer.append("\n	  }");
                    cmBuffer.append("\n	,{");
                    cmBuffer.append("\n	     name :'YOYRATION',");
                    cmBuffer.append("\n	     header : '同比', ");
                    cmBuffer.append("\n	     dataIndex: 'YOYRATION',");
                    cmBuffer.append("\n	     sortable : false ");
                    cmBuffer.append("\n	  }");
                    cmBuffer.append("\n	,{");
                    cmBuffer.append("\n	     name :'MONRATION',");
                    cmBuffer.append("\n	     header : '环比', ");
                    cmBuffer.append("\n	     dataIndex: 'MONRATION',");
                    cmBuffer.append("\n	     sortable : false ");
                    cmBuffer.append("\n	  }");
                    cmBuffer.append("\n	,{");
                    cmBuffer.append("\n	     name :'TYOYRATION',");
                    cmBuffer.append("\n	     header : '同比累计', ");
                    cmBuffer.append("\n	     dataIndex: 'TYOYRATION',");
                    cmBuffer.append("\n	     sortable : false ");
                    cmBuffer.append("\n	  }");
                    cmBuffer.append("\n	,{");
                    cmBuffer.append("\n	     name :'TMONRATION',");
                    cmBuffer.append("\n	     header : '环比累计', ");
                    cmBuffer.append("\n	     dataIndex: 'TMONRATION',");
                    cmBuffer.append("\n	     sortable : false ");
                    cmBuffer.append("\n	  }");
                    cmBuffer.append("\n	,{");
                    cmBuffer.append("\n	     name :'PREVAL',");
                    cmBuffer.append("\n	     header : '上期值', ");
                    cmBuffer.append("\n	     dataIndex: 'PREVAL',");
                    cmBuffer.append("\n	     sortable : false ");
                    cmBuffer.append("\n	  }");
                    cmBuffer.append("\n	,{");
                    cmBuffer.append("\n	     name :'TPREVAL',");
                    cmBuffer.append("\n	     header : '上期累计', ");
                    cmBuffer.append("\n	     dataIndex: 'TPREVAL',");
                    cmBuffer.append("\n	     sortable : false ");
                    cmBuffer.append("\n	  }");
                    cmBuffer.append("\n	,{");
                    cmBuffer.append("\n	     name :'TREPVAL',");
                    cmBuffer.append("\n	     header : '同期值', ");
                    cmBuffer.append("\n	     dataIndex: 'TREPVAL',");
                    cmBuffer.append("\n	     sortable : false ");
                    cmBuffer.append("\n	  }");
                    cmBuffer.append("\n	,{");
                    cmBuffer.append("\n	     name :'TSUMVAL',");
                    cmBuffer.append("\n	     header : '同期累计', ");
                    cmBuffer.append("\n	     dataIndex: 'TSUMVAL',");
                    cmBuffer.append("\n	     sortable : false ");
                    cmBuffer.append("\n	  }");
                }

                strBuffer.append("\n ] }); ");
                cmBuffer.append("\n ]);");

                rest.append(strBuffer.toString());
                rest.append(cmBuffer.toString());
                rest.append(getCombTreeJS(repVo,corpid));
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return rest.toString();
    }
    
    //导出表格所需要的SQL语句
    public String getDataGrid(String repid,String corpid,String demJsonArr) {
       String res = "";
           RepDataVO repVo = getRepDemension(repid);
           if (repVo != null) {
           String tablename = repVo.getReffact(); //TF_REP_HRCNT
           res=tablename;
           FactTableVO.setSql(res);
           }
       
       return res;
   }
    
    public String getCombTreeJS(RepDataVO repVo,String corpid){
    	StringBuffer treeBuffer = new StringBuffer();   //ComboBoxTree
    	StringBuffer panBuffer = new StringBuffer();   //panel
    	try {
			ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
			if (repVo==null || repDemLst==null || repDemLst.size()==0){
				return "";
			}
            panBuffer.append("\n    var dimLen = "+repDemLst.size()+";");
			panBuffer.append("\n    var p_wdxx = new Ext.FormPanel({");
			panBuffer.append("\n				id : 'p_wdxx',");
			panBuffer.append("\n				layout : 'form',");
			panBuffer.append("\n				labelWidth : 80,");
			panBuffer.append("\n				width : '98%',height: '100%',");
//			panBuffer.append("\n				anchor:'-0,-0',");
			panBuffer.append("\n				bodyStyle : 'padding: 4px',");
			panBuffer.append("\n				border : false,frame :true,");
			panBuffer.append("\n				autoScroll : true,");
			panBuffer.append("\n				items : [{");
			panBuffer.append("\n				title : '筛选维度',");
			panBuffer.append("\n				layout : 'form',");
			panBuffer.append("\n				xtype : 'fieldset',");
			panBuffer.append("\n				collapsible : true,");
			panBuffer.append("\n				collapsed : false,");
			panBuffer.append("\n				labelAlign : 'right',");
			panBuffer.append("\n				height: '100%',");
			panBuffer.append("\n				defaults : {");
			panBuffer.append("\n					width : '100%'");
			panBuffer.append("\n				},");
			panBuffer.append("\n				items : [");
//			String icolumnWidth = ".35";
//			if (repDemLst.size()>0){
//				icolumnWidth = NumberFormatUtils.divide("1", String.valueOf(repDemLst.size()));
//			}
//			System.out.println("icolumnWidth:"+icolumnWidth);
			for (int i = 0; i < repDemLst.size(); i++) {
				RepDemensionVO repDemVO = repDemLst.get(i);
			    String demidcol = repDemVO.getIdcol();
			    String demname = repDemVO.getDemname();
			    String availsname = repDemVO.getAvailsname();
			    String demtype = repDemVO.getDemtype();
			    String facdimvalue = "";
//			    if (demtype==null || demtype.equals("")){
//			    	demtype = "3";
//			    }else if (demtype.equals("1")){
//			    	RepDemHirecVO hVo = getDimHirecByID(repDemVO,corpid);
//			    	if (hVo!=null){
//			    		facdimvalue = hVo.getCode();
//			    	}
//			    }else if (demtype.equals("2")){
//			    	RepDemHirecVO hVo = getDimHirecByID(repDemVO,"2012");
//			    	if (hVo!=null){
//			    		facdimvalue = hVo.getCode();
//			    	}
//			    }
			    if (demidcol != null && !demidcol.equals("")) {
			    	treeBuffer.append("\n    	var selectedNode"+i+" = null;");
			    	treeBuffer.append("\n		var comb"+i+" = new Ext.form.ComboBox(");
			    	treeBuffer.append("\n						{");
			    	treeBuffer.append("\n							id : 'comb"+i+"',");
			    	treeBuffer.append("\n							store : new Ext.data.SimpleStore( {");
			    	treeBuffer.append("\n								fields : [],");
			    	treeBuffer.append("\n								data : [ [] ]");
			    	treeBuffer.append("\n							}),");
			    	treeBuffer.append("\n							editable : false,");
			    	treeBuffer.append("\n							fieldLabel : '"+availsname+"',");
			    	treeBuffer.append("\n							shadow : false,");
			    	treeBuffer.append("\n							width : 400,");
			    	treeBuffer.append("\n							mode : 'local',");
			    	treeBuffer.append("\n							canCollapse:true,");
			    	treeBuffer.append("\n							emptyText : '请选择"+availsname+"',");
			    	treeBuffer.append("\n							anchor : '100%',width : '100%',");
			    	treeBuffer.append("\n							triggerAction : 'all',");
			    	treeBuffer.append("\n							tpl : '<tpl for=\".\"><div style=\"height:180px\"><div id=\"treediv_"+i+"\"></div></div></tpl>',");
			    	treeBuffer.append("\n							selectedClass : ''");
//			    	treeBuffer.append("\n							,onSelect : Ext.emptyFn");
			    	treeBuffer.append("\n						});");
			    	treeBuffer.append("\n");
			    	treeBuffer.append("\n				var root"+i+" = new Ext.tree.AsyncTreeNode( {");
			    	treeBuffer.append("\n					text : '"+demname+"',");
			    	treeBuffer.append("\n					id : -1");
			    	treeBuffer.append("\n				});    ");
			    	treeBuffer.append("\n		");
			    	treeBuffer.append("\n			var tree"+i+" = new Ext.tree.TreePanel(");
			    	treeBuffer.append("\n			{");
			    	treeBuffer.append("\n							border : '0',");
			    	treeBuffer.append("\n							height : 180,");
			    	treeBuffer.append("\n							useArrows : true, // 使用箭头");
			    	treeBuffer.append("\n							autoScroll : true,");
			    	treeBuffer.append("\n							animate : true, // 动画");
			    	treeBuffer.append("\n							enableDD : false, // 可拖动");
			    	treeBuffer.append("\n							containerScroll : true,");
			    	treeBuffer.append("\n							rootVisible : false,");
			    	treeBuffer.append("\n							root : root"+i+",");
			    	treeBuffer.append("\n							autoScroll : true,");
			    	treeBuffer.append("\n							lines : false,");
			    	treeBuffer.append("\n							iconCls : 'nav',");
//			    	treeBuffer.append("\n							checkModel : 'cascade',");
			    	treeBuffer.append("\n							onlyLeafCheckable : false,");
//			    	treeBuffer.append("\n							tbar : [new Ext.Toolbar.SplitButton({text:'快速选择',menu:{items:[{text:'选择下级节点',handler:function(){if(treehandler!=null)treehandler(1);}},{text:'选择全部子节点',handler:function(){if(treehandler!=null)treehandler(2);}},{text:'取消下级节点',handler:function(){if(treehandler!=null)treehandler(-1);}},{text:'选择全部子节点',handler:function(){if(treehandler!=null)treehandler(-2);}}]}})],");
			    	treeBuffer.append("\n							titleCollapse : true,");
			    	treeBuffer.append("\n							listeners : {");
			    	treeBuffer.append("\n								beforeload : function(node) {");
			    	treeBuffer.append("\n									var openUrl"+i+" = basePath + 'JarpListener?className=cn.com.jsepc.rep.report.QueryData&methodName=getDemTreeData&paramCount=5'");
			    	treeBuffer.append("\n									openUrl"+i+" += '&param_0="+repDemVO.getDimtable()+"&param_1="+repDemVO.getParentcol()+"&param_2='+node.id+'&param_3="+repDemVO.getDimid()+"&param_4='+corpid");
			    	treeBuffer.append("\n									node.loader = new Ext.tree.TreeLoader( {");
			    	treeBuffer.append("\n										dataUrl : openUrl"+i+",");
			    	treeBuffer.append("\n										baseAttrs : {");
			    	treeBuffer.append("\n											uiProvider : Ext.tree.TreeCheckNodeUI");
			    	treeBuffer.append("\n										}");
			    	treeBuffer.append("\n									});");
			    	treeBuffer.append("\n								}");
			    	treeBuffer.append("\n							}");
			    	treeBuffer.append("\n						});");
			    	treeBuffer.append("\n				comb"+i+".on('expand', function() {");
			    	treeBuffer.append("\n					tree"+i+".render('treediv_"+i+"');");
			    	treeBuffer.append("\n					comb"+i+".canCollapse = true;");
			    	treeBuffer.append("\n					if (tree"+i+") {");
			    	treeBuffer.append("\n						comb"+i+".canCollapse = true;");
			    	treeBuffer.append("\n						tree"+i+".getRootNode().expand();");
			    	treeBuffer.append("\n					}");
			    	treeBuffer.append("\n				});");
			    	treeBuffer.append("\n				tree"+i+".on('check', function(node) {	");
			    	treeBuffer.append("\n					comb"+i+".canCollapse = false;");
			    	//开始树的操作
                    treeBuffer.append("if(node.isExpanded()){");   //树是展开状态
                    treeBuffer.append("if(node.attributes.checked){");
                    treeBuffer.append(" node.eachChild(function(child) {  ");
                    treeBuffer.append("child.ui.toggleCheck(true);");
                    treeBuffer.append("child.attributes.checked = true;");
                    treeBuffer.append("child.fireEvent('checkchange', child, true);");
                    treeBuffer.append("});");
                    treeBuffer.append("}");
                    treeBuffer.append("}else{");  //树是闭合状态
                    treeBuffer.append(" node.eachChild(function(child) {  ");
                    treeBuffer.append("child.ui.toggleCheck(false);");
                    treeBuffer.append("child.attributes.checked = false;");
                    treeBuffer.append("child.fireEvent('checkchange', child, false);");
                    treeBuffer.append("});");
                    treeBuffer.append("}");
			    	treeBuffer.append("\n				});	");
			    	treeBuffer.append("\n               comb"+i+".on('collapse', function() {");
			    	treeBuffer.append("\n					if (!comb"+i+".canCollapse) {");
			    	treeBuffer.append("\n						comb"+i+".expand();");
			    	treeBuffer.append("\n					} else{");
			    	treeBuffer.append("\n					var bdm = '';");
			    	treeBuffer.append("\n					var bmc = '';");
			    	treeBuffer.append("\n					var checked = tree"+i+".getChecked();");
			    	treeBuffer.append("\n					if (checked.length > 0) {");
			    	treeBuffer.append("\n						for (var i = 0; i < checked.length - 1; i++) {");
			    	treeBuffer.append("\n							var node = checked[i];");
//			    	treeBuffer.append("\n							if (node.isLeaf()) {");
			    	treeBuffer.append("\n								bdm += node.id + \",\";");
			    	treeBuffer.append("\n								bmc += node.text + \",\";");
//			    	treeBuffer.append("\n							}");
			    	treeBuffer.append("\n						}");
			    	treeBuffer.append("\n						bdm += checked[checked.length - 1].id;");
			    	treeBuffer.append("\n						bmc += checked[checked.length - 1].text;");
			    	treeBuffer.append("\n					}");
                    treeBuffer.append("\n                   Ext.ComponentMgr.get(\"comb").append(i).append("\").setValue(bmc);");
                    treeBuffer.append("\n                   Ext.ComponentMgr.get(\"facdimvalue").append(i).append("\").setValue(bdm);");
                    treeBuffer.append("\n                   Ext.ComponentMgr.get(\"facdimtable").append(i).append("\").setValue('"+repDemVO.getDimtable()+"');");
                    treeBuffer.append("\n                   Ext.ComponentMgr.get(\"facdimcodecol").append(i).append("\").setValue('"+repDemVO.getIdcol()+"');");
                    treeBuffer.append("\n                   Ext.ComponentMgr.get(\"facdimfield").append(i).append("\").setValue('"+repDemVO.getFactdimcol()+"');");
			    	treeBuffer.append("\n				}});");
			    	treeBuffer.append("\n				tree"+i+".on('expandnode', function() { comb"+i+".canCollapse = true; });");
			    	treeBuffer.append("\n				tree"+i+".on('beforeload', function() { comb"+i+".canCollapse = false; });");
			    	treeBuffer.append("\n				tree"+i+".on('beforeexpandnode', function() { comb"+i+".canCollapse = false; });");
			    	treeBuffer.append("\n				tree"+i+".on('beforecollapsenode', function() { comb"+i+".canCollapse = false; });");
                    
			        if (i!=0){
			        	panBuffer.append("\n									,");
			        }
			        panBuffer.append("\n							{   layout : 'column',");
					panBuffer.append("\n								border : false,");
					panBuffer.append("\n								items : [");
					panBuffer.append("\n										{	columnWidth : .7,");
					panBuffer.append("\n											layout : 'form',");
					panBuffer.append("\n											labelWidth : 80,");
					panBuffer.append("\n											anchor : '100%',");
					panBuffer.append("\n											width : '100%',");
					panBuffer.append("\n											border : false,");
					panBuffer.append("\n											bodyStyle : 'padding:4px 4px 0',");
					panBuffer.append("\n											items : [comb"+i+"");
                    panBuffer.append("\n											,{xtype:'hidden',   id:'facdimtable"+i+"',value:'"+repDemVO.getDimtable()+"'}");
                    panBuffer.append("\n											,{xtype:'hidden',   id:'facdimcodecol"+i+"',value:'"+repDemVO.getIdcol()+"'}");
					panBuffer.append("\n											,{xtype:'hidden',   id:'facdimvalue"+i+"',value:'"+facdimvalue+"'}");
					panBuffer.append("\n											,{xtype:'hidden',   id:'facdimfield"+i+"',value:'"+repDemVO.getFactdimcol()+"'}");
					panBuffer.append("\n										]}");
					panBuffer.append("\n						]}");
			    }
			}
			
			panBuffer.append("\n ,{xtype:'checkbox',id:'iswcz',fieldLabel:'完成值'}");
			
			panBuffer.append("\n			]}");
			panBuffer.append("\n]});");
			
			//System.out.println(treeBuffer.toString()+panBuffer.toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return treeBuffer.toString()+panBuffer.toString();
    }
 
    
    /**
     * 维度成员树信息
     * @param table
     * @param parentcol
     * @param parentid
     * @return
     */
    public String getDemTreeData(String table,String parentcol,String parentid,String dimid ,String corpid) {
        String res = "";
        JSONArray json = new JSONArray();
        StringBuffer sqls = new StringBuffer();
        try {
            DAO dao = new DAO();
            DemensionVO demVo = getDemension(dimid);
            if (demVo!=null){
            	String dimidcol = demVo.getIdcol();
            	String dimcodecol = demVo.getCodecol();
            	String dimnamecol = demVo.getNamecol();
            	String dimtype = demVo.getDimtype();
            	sqls.append("SELECT * FROM "+table+" where 1=1 ");
            	if (corpid!=null && dimtype!=null && dimtype.equals("1") && parentid.equals("-1")){
            		String dimcodeval = this.getDimCodeByID(demVo.getDimtable(), dimidcol, dimcodecol, corpid);
            		if (dimcodeval!=null && !dimcodeval.equals("")){
            			sqls.append(" and "+dimcodecol+"='"+dimcodeval+"'");
            		}
            	}else{
            		sqls.append(" and  "+parentcol+"="+parentid+"");
            	}
	            List<DemTreeVO1> lstResults = new ArrayList<DemTreeVO1>();
	            sqls.append(" order by "+dimidcol);
	            Vector v = dao.getDataSet(ConstUtil.DBNAMEODS, sqls.toString(),0,0);
	            if (v != null) {
	                for (int i = 0; i < v.size(); i++) {
	                    Hashtable hashRows = (Hashtable)v.get(i);
	                    DemTreeVO1 treeVO = new DemTreeVO1();
	                    String dimidval = (String) hashRows.get(dimidcol);
	                    treeVO.setId((String) hashRows.get(dimidcol));//dimidcol dimcodecol
	                    treeVO.setText((String) hashRows.get(dimnamecol));
	                    treeVO.setCode((String) hashRows.get(dimcodecol));
	                    boolean isLeaf=this.isLeaf(dimidval, parentcol, table);
	                    treeVO.setLeaf(isLeaf);
	                    treeVO.setCheck(false);
	                    lstResults.add(treeVO);
	                }
	                json = JSONArray.fromObject(lstResults);
	            }
            }
            res = json.toString();
        } catch (ServletException ex) {
            Logger.getLogger(DementionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return res;
    }
    /**
     * 加载指标对象
     * @param repid
     * @return
     */
    public RepDataVO getRepDemension(String repid) {
        RepDataVO repVO = new RepDataVO();
        DAO dao = new DAO();
        try {
            String sql = "select * from ( select * from tb_report where repid=" + repid + " and ISVALID=1 ) a"
                    + " left join tb_rep_demension b on b.repid=a.repid"
                    + " left join tb_demension c on c.demid=b.dimid order by b.dimorder";
            Vector v = dao.getDataSet(ConstUtil.DBNAME, sql);
            if (v != null) {
                ArrayList<RepDemensionVO> repDemLst = new ArrayList<RepDemensionVO>();
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hashRows = (Hashtable) v.get(i);
                    RepDemensionVO repDemVO = new RepDemensionVO();
                    repDemVO.setId((String) hashRows.get("ID"));
                    repDemVO.setRepid((String) hashRows.get("REPID"));
                    repDemVO.setDimid((String) hashRows.get("DIMID"));
                    repDemVO.setHirecdesc((String) hashRows.get("HIRECDESC"));
                    repDemVO.setDemname((String) hashRows.get("DEMNAME"));
                    repDemVO.setFactdimcol(((String) hashRows.get("FACDIMCOL")).toUpperCase());
                    repDemVO.setAvailsname((String) hashRows.get("AVAILSNAME"));
                    repDemVO.setDimorder((String) hashRows.get("DIMORDER"));
                    repDemVO.setDemcode((String) hashRows.get("DEMCODE"));
                    repDemVO.setDimtable((String) hashRows.get("DIMTABLE"));
                    repDemVO.setIdcol(((String) hashRows.get("IDCOL")).toUpperCase());
                    repDemVO.setNamecol(((String) hashRows.get("NAMECOL")).toUpperCase());
                    repDemVO.setParentcol(((String) hashRows.get("PARENTCOL")).toUpperCase());
                    repDemVO.setCodecol(((String) hashRows.get("CODECOL")).toUpperCase());
                    repDemVO.setDemtype((String) hashRows.get("DIMTYPE"));
                    repDemVO.setIsvalid((String) hashRows.get("ISVALID"));
                    repDemVO.setLevelcol((String) hashRows.get("LEVELCOL"));
                    repDemLst.add(repDemVO);
                    if (i == v.size() - 1) {
                        repVO.setRepid((String) hashRows.get("REPID"));
                        repVO.setRepcode((String) hashRows.get("REPCODE"));
                        repVO.setRepname((String) hashRows.get("REPNAME"));
                        repVO.setCataid((String) hashRows.get("CATAID"));
                        repVO.setRepdesc((String) hashRows.get("REPDESC"));
                        repVO.setDesc((String) hashRows.get("CALDESC"));
                        repVO.setDataprec((String) hashRows.get("DATAPREC"));
                        repVO.setDataunit((String) hashRows.get("DATAUNIT"));
                        repVO.setDepartment((String) hashRows.get("DEPARTMENT"));
                        repVO.setSourcetype((String) hashRows.get("SOURCETYPE"));
                        repVO.setDatatype((String) hashRows.get("DATATYPE"));
                        repVO.setUpdatetime((String) hashRows.get("UPDATETIME"));
                        repVO.setReffact((String) hashRows.get("REFFACT"));
                        repVO.setRepvaluecol(((String) hashRows.get("REPVALUECOL")).toUpperCase());
                        repVO.setIssum((String) hashRows.get("ISSUM"));
                        repVO.setIsvalid((String) hashRows.get("ISVALID"));
                        repVO.setRepDemLst(repDemLst);
                    }
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            System.out.println("QueryData getFillGrid Exception.");
        }
        return repVO;
    }
    public DemensionVO getDemension(String dimid) {
        DAO dao = new DAO();
        DemensionVO demVO = null;
        try {
            String sql = "select * from tb_demension where DEMID=" + dimid ;
            Vector v = dao.getDataSet(ConstUtil.DBNAME, sql);
            if (v != null && v.size()>0) {
            	demVO = new DemensionVO();
                    Hashtable hashRows = (Hashtable) v.get(0);
                    demVO.setDemid((String) hashRows.get("DEMID"));
                    demVO.setHirecdesc((String) hashRows.get("HIRECDESC"));
                    demVO.setDemname((String) hashRows.get("DEMNAME"));
                    demVO.setDemcode((String) hashRows.get("DEMCODE"));
                    demVO.setDimtable((String) hashRows.get("DIMTABLE"));
                    demVO.setIdcol(((String) hashRows.get("IDCOL")).toUpperCase());
                    demVO.setNamecol(((String) hashRows.get("NAMECOL")).toUpperCase());
                    demVO.setParentcol(((String) hashRows.get("PARENTCOL")).toUpperCase());
                    demVO.setCodecol(((String) hashRows.get("CODECOL")).toUpperCase());
                    demVO.setDimtype((String) hashRows.get("DIMTYPE"));
                    demVO.setIsValid((String) hashRows.get("ISVALID"));                  
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            System.out.println("QueryData getDemension Exception.");
        }
        return demVO;
    }

    /**
     * 加载指标数据
     * @param start
     * @param limit
     * @param repid
     * @param corpid
     * @return
     */
    public String getDataGrid(String start, String limit, String repid,String corpid) {
        return getDataGrid( start,  limit,  repid, corpid,"");
    }
    public String getDataGrid(String start, String limit, String repid,String corpid,String demJsonArr) {
    	String factsql=this.getDataGrid(repid, corpid, demJsonArr);
        String res = "";
        DAO dao = new DAO();
        JSONArray json = new JSONArray();
        String totalCount = null;
        try {
            int iStart = Integer.parseInt(start);
            int iPageSize = Integer.parseInt(limit);
            int iPage = iStart/iPageSize + 1;
            StringBuffer demwhereBuffer = new StringBuffer();
            if (demJsonArr!=null && !demJsonArr.equals("")){
            	JSONObject qryjson = JSONObject.fromString(demJsonArr);
	            JSONArray demArr = qryjson.getJSONArray("demlist");
	            for (int i = 0; i < demArr.length(); i++)
	            {
	            	JSONObject jsonDem = demArr.getJSONObject(i);
	                String facdimtable = jsonDem.getString("facdimtable");
	                String facdimcodecol = jsonDem.getString("facdimcodecol");
	                String facdimvalue = jsonDem.getString("facdimvalue");
	                if (facdimvalue!=null && !facdimvalue.equals("")){
	                	demwhereBuffer.append("\n and "+facdimtable+"."+facdimcodecol+" in ("+facdimvalue+")");
	                }
	            }
            }else{
            	System.out.println("demJsonArr is null");
            }
            RepDataVO repVo = getRepDemension(repid);
            if (repVo != null) {
            String tablename = repVo.getReffact(); //TF_REP_HRCNT
            String faccol = repVo.getRepvaluecol(); //HTCNT
            String facsumcol = "SUMVAL";
//            String issum = repVo.getIssum();//1聚合0不聚合
            String dwDimCodeCol = null;
            String dwDimCodeVal = null;
            boolean bDwDim = false;
//            boolean bIsSum = false;//是否聚合
//            if (issum!=null && issum.equals("1")){
//            	bIsSum = true;
//            }
            ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
            if (tablename!=null && faccol != null && repDemLst!=null && repDemLst.size()>0) {
            StringBuffer ssql = new StringBuffer();
            StringBuffer ssqlTot = new StringBuffer();
            StringBuffer sdemcol = new StringBuffer();
            StringBuffer sdemtab = new StringBuffer();
            StringBuffer sdemon = new StringBuffer();
            StringBuffer sorder = new StringBuffer();
            ssql.append("select a."+faccol+",a."+facsumcol+",a.PREVAL,a.TPREVAL,a.TREPVAL,a.TSUMVAL");
            for (int j = 0; j < repDemLst.size(); j++) {
                RepDemensionVO repDemVO = repDemLst.get(j);
                String dimtable = repDemVO.getDimtable(); //td_org
                String dimidcol = repDemVO.getIdcol();
                String facdimcol = repDemVO.getFactdimcol();  //ORG_ID
                String dimcodecol = repDemVO.getCodecol();
                String dimnamecol = repDemVO.getNamecol();
                ssql.append("\n ,z."+facdimcol);
                ssql.append("\n ,z."+facdimcol+dimnamecol);
                if (j!=0){
                	sdemtab.append(",");
                	sdemon.append(" and ");
                	sorder.append(",");
                	sdemcol.append("\n,");
                }
                sdemcol.append(""+dimtable+"."+dimidcol +" as "+ facdimcol);
                sdemcol.append("\n,"+dimtable+"."+dimnamecol +" as "+ facdimcol+dimnamecol);
                
                sdemtab.append(dimtable);
                sdemon.append(" z."+facdimcol + " = a."+facdimcol);
                sorder.append(facdimcol);
	            String dimtype = repDemVO.getDemtype();//1单位维度 2时间维度 3其他维度
	            if (dimtype!=null){
	              	if (dimtype.equals("1")){	                	
		                	if (dimcodecol!=null && !dimcodecol.equals("")){
		                		bDwDim = true;
		                		dwDimCodeVal = getDimCodeByID(dimtable,dimidcol,dimcodecol,corpid);
		                		dwDimCodeCol = dimtable+"."+dimcodecol;
		                	}
	              	}
	              }
            }            
            ssql.append("\n from (select ");     
            ssql.append(sdemcol.toString()); 
            ssql.append("\n from "+sdemtab.toString() + " where 1=1 ");     
            if (corpid!=null && !corpid.equals("") && bDwDim){
            	if (dwDimCodeVal!=null && !dwDimCodeVal.equals("")){
            		ssql.append(" and "+dwDimCodeCol+" like '"+dwDimCodeVal+"%'");
            	}
            }
            ssql.append(demwhereBuffer.toString());
            ssql.append("\n ) z ");     
            ssql.append("\n left join "+tablename +" a on "+sdemon);
            ssql.append("\n where a.repval is not null or sumval is not null ");
            ssql.append("\n order by "+sorder.toString());
            Vector v = dao.getDataSet(ConstUtil.DBNAMEODS, ssql.toString(),iPage,iPageSize);
            if (v != null) {
               json =  JSONArray.fromObject(v);
            }
            ssqlTot.append("select count(1) CNT from (" + ssql.toString() +" ) zz ");
             totalCount = dao.getDataValue(ConstUtil.DBNAMEODS, ssqlTot.toString());
            }
            }
            res = "{totalCount:" + totalCount + ",data:" + json.toString() + "}";
        } catch (Exception ex) {
            ex.printStackTrace();
            System.out.println("QueryData getDataGrid Exception.");
        }
        return res;
    }

    
    
    //修改
    public synchronized String DatatbUpdate(String repid,String itemJsonInfo) throws ServletException {
        String ret = "";
        DAO dao = new DAO();
        Vector getParam=new Vector();   //结果集（Hashtable）
        String [] DemRow=null;   //维度列名  数组
        
        String get_Tim_unit="SELECT c.FACDIMCOL FROM "
                + "tb_report a"
                + " LEFT JOIN tb_rep_demension b on a.REPID=b.REPID "
                + "LEFT JOIN tb_demension c on c.FACDIMCOL=b.FACTDIMCOL"
                + " WHERE a.REPID="+repid+" AND c.DIMTYPE=2";
        try {
            String time_col=dao.getDataValue(ConstUtil.DBNAME, get_Tim_unit);
             ArrayList<FieldDataVO> lstResults = new ArrayList<FieldDataVO>();     
            ArrayList<String> sqls = new ArrayList<String>();
            JSONObject jsonObject = new JSONObject(itemJsonInfo);
            
            RepDataVO repVo = getRepDemension(repid);
            if (repVo == null) {
                return null;
            }
            ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
            if (repDemLst == null || repDemLst.size()==0) {
                return null;
            }
            DemRow=new String[repDemLst.size()];
            for (int j = 0; j < repDemLst.size(); j++) {
                    RepDemensionVO repDemVO = repDemLst.get(j);
                    String dimidcol = repDemVO.getFactdimcol();  //ORG_ID
                    DemRow[j]=dimidcol;
                }
            String tablename = repVo.getReffact(); //TF_REP_HRCNT
            String faccol = repVo.getRepvaluecol(); //HTCNT
            String facsumcol = "SUMVAL";
            JSONArray jsonArr = jsonObject.getJSONArray("listData");
            
            for (int i = 0; i < jsonArr.length(); i++) {
                Hashtable paraHash=new Hashtable();  //结果集的里的字段
                JSONObject jsonOne = jsonArr.getJSONObject(i);
                StringBuffer strquery = new StringBuffer();
                StringBuffer strupdate = new StringBuffer();
                StringBuffer strupdatewhere = new StringBuffer();
                StringBuffer strinsert = new StringBuffer();
                StringBuffer strinsertval = new StringBuffer();
                String repvalue = jsonOne.getString(faccol);
                if(repvalue==null || repvalue.equals("")){ 
                	repvalue = "NULL";
                }
                String facsumval = jsonOne.getString(facsumcol);
                if(facsumval==null || facsumval.equals("")){ 
                	facsumval = "NULL";
                }
                strquery.append("select "+faccol+" from "+tablename);
                strupdate.append("update " + tablename + " set " + faccol + "=" + repvalue +" ");
                strupdate.append(" ,"+facsumcol+"="+facsumval );
                strupdate.append(" where ");
                strinsert.append("insert into " + tablename +" ( ");
                paraHash.put(faccol, repvalue);
                paraHash.put(facsumcol, facsumval);
                for (int j = 0; j < repDemLst.size(); j++) {
                    RepDemensionVO repDemVO = repDemLst.get(j);
                    String dimtable = repDemVO.getDimtable(); //td_org
                    String dimidcol = repDemVO.getFactdimcol();  //ORG_ID
                    String colvalue = jsonOne.getString(dimidcol);
                    if (j!=0){
                    	strupdatewhere.append(" and ");
                    	strinsert.append(",");
                    	strinsertval.append(",");
                    }
                    strinsert.append(" "+dimidcol);
                    strupdatewhere.append(" "+dimidcol+" = "+colvalue+"");
                    strinsertval.append(" "+colvalue);
                    paraHash.put(dimidcol, colvalue);
                     
                }
                strquery.append("\n where "+strupdatewhere.toString());
                Vector v = dao.getDataSet(ConstUtil.DBNAMEODS, strquery.toString());
	            if (v != null && v.size()>0) {
	            	strupdate.append(strupdatewhere.toString());
	            	sqls.add(strupdate.toString());
	            }else{
	            	if(repvalue==null || repvalue.equals("")){ 
	                	repvalue = "0";
	                }
	            	if(facsumval==null || facsumval.equals("")){ 
	            		facsumval = "0";
	                }
	            	strinsert.append(" ,"+faccol+","+facsumcol+") values ("+strinsertval.toString()+" ,"+repvalue+","+facsumval+") ");
	            	sqls.add(strinsert.toString());
	            }
                    
                getParam.add(paraHash);
            }
            
            /*System.out.println("getParam:"+getParam);
             * 向后台传输数据
             * 所需参数：
             * getParam 结果集
             * time_col:时间字段名
             * DemRow维度列名  数组
             * 事实表表名
             * tablename
             */
            if (sqls.size() > 0) {
                if (dao.execCommand(ConstUtil.DBNAMEODS, sqls)) {
                     cal.CalcValues(tablename, time_col, DemRow, getParam); 
                    ret = "ok";
                } else {
                    ret = "fail";
                }
            } else {
                ret = "no";
            }
          
        } catch (RuntimeException e) {
            e.printStackTrace();
        }
        return ret;
    }
    
    public boolean isLeaf(String ID,  String parent_id_colum_name, String table_name) {
        boolean bLeaf = false;
        try {
            DAO dao=new DAO();
            String sql="SELECT count(1) CC from "+table_name+" Where "+parent_id_colum_name+"="+ID+"";
            String cc=dao.getDataValue(ConstUtil.DBNAMEODS, sql);
            if(Integer.parseInt(cc)==0)
                bLeaf = true;
            
        } catch (ServletException ex) {
            Logger.getLogger(CheckLeaf.class.getName()).log(Level.SEVERE, null, ex);
            
        }
        return bLeaf;
    }
    
    public String getDimCodeByID(String dimtablename,String dimidcol,String dimcodecol,String dimidval) {
    	String sret = "";
        try {          
            DAO dao=new DAO();
            String sql="select "+dimcodecol+" from "+dimtablename+" where "+dimidcol+"="+dimidval+"";
            sret=dao.getDataValue(ConstUtil.DBNAMEODS, sql);
            
        } catch (ServletException ex) {
            Logger.getLogger(CheckLeaf.class.getName()).log(Level.SEVERE, null, ex);
            
        }
        return sret;
    }
    public String getDimIdByCode(String dimtablename,String dimidcol,String dimcodecol,String dimcodeval) {
    	String sret = "-1";
        try {        
        	if (dimcodeval!=null && !dimcodeval.equals("-1") && !dimcodeval.equals("")){
	            DAO dao=new DAO();
	            String sql="select "+dimidcol+" from "+dimtablename+" where "+dimcodecol+"='"+dimcodeval+"'";
	            sret=dao.getDataValue(ConstUtil.DBNAMEODS, sql);
        	}
            
        } catch (ServletException ex) {
            Logger.getLogger(CheckLeaf.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sret;
    }
    public String getDimPCodeByID(String dimtablename,String dimidcol,String dimpcodecol,String dimidval) {
    	String sret = "";
        try {          
            DAO dao=new DAO();
            String sql="select "+dimpcodecol+" from "+dimtablename+" where "+dimidcol+"="+dimidval+"";
            sret=dao.getDataValue(ConstUtil.DBNAMEODS, sql);
            
        } catch (ServletException ex) {
            Logger.getLogger(CheckLeaf.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sret;
    }
    public RepDemHirecVO getDimHirecByID(RepDemensionVO demVo,String dimidval) {
    	RepDemHirecVO hVo = null;
        try {          
            DAO dao=new DAO();
            if (demVo!=null){
	            String sql="select * from "+demVo.getDimtable()+" where "+demVo.getIdcol()+"="+dimidval+"";
	            Vector v = dao.getDataSet(ConstUtil.DBNAMEODS, sql);
	            if (v != null && v.size()>0)
	            {
	                    Hashtable hashRows = (Hashtable)v.get(0);
	                    hVo = new RepDemHirecVO();
	                    hVo.setRepid(demVo.getRepid());
	                    hVo.setDimid(demVo.getDimid());
	                    hVo.setId((String)hashRows.get(demVo.getIdcol()));
	                    hVo.setName((String)hashRows.get(demVo.getNamecol()));
	                    hVo.setLevel((String)hashRows.get(demVo.getLevelcol()));
	                    hVo.setPcode((String)hashRows.get(demVo.getParentcol()));
	                    hVo.setCode((String)hashRows.get(demVo.getCodecol()));	                    
	            }
            }
        } catch (ServletException ex) {
            Logger.getLogger(CheckLeaf.class.getName()).log(Level.SEVERE, null, ex);
        }
        return hVo;
    }
    
}
